SELECT t.*
FROM c1_role t
WHERE EXISTS
(SELECT 1 FROM c1_role_user e WHERE e.role_id = t.id AND e.user_id = :userId
)
OR EXISTS
(SELECT 1
FROM c1_us_group_role e
WHERE e.role_id = t.id
AND EXISTS
(SELECT 1
FROM c1_us_group_user e_e
WHERE e_e.user_group_id = e.user_group_id
AND e_e.user_id         = :userId
)
)
ORDER BY t.org_id,
t.department_id,
t.order_num